IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[or_MemaUserAuth]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.[or_MemaUserAuth]
Go

CREATE  PROCEDURE  dbo.[or_MemaUserAuth]  
	@Email		nvarchar(100),
	@Password		nvarchar(100)

AS  
DECLARE 
   @TranCountAtEntry int, @DateTime datetime, @UserID int, @Authenticated bit
  

BEGIN TRY

SELECT @TranCountAtEntry = @@TRANCOUNT
SELECT @DateTime  = getdate() 
SELECT @Authenticated = 0

IF @TranCountAtEntry  = 0 
BEGIN TRANSACTION 

SELECT  MeMaUserID				,
	UserTypeID		,				
	FirstName			,
	LastName			,
	Email					,
	Password				,
	IsActive		
FROM MemaUser 
WHERE 
UPPER(LTRIM(RTRIM(Email))) = UPPER(LTRIM(RTRIM(@Email)))
AND LTRIM(RTRIM(Password))= LTRIM(RTRIM(@Password))

 


IF @@TRANCOUNT > @TranCountAtEntry
COMMIT TRANSACTION

END TRY
BEGIN CATCH
  
  IF @@TRANCOUNT > @TranCountAtEntry
  ROLLBACK TRANSACTION

  -- Raise an error with the details of the exception
  or_raiseerror 

END CATCH
  